<?php

/**
 * Tools_StatsObject - Classe de calcul des statistiques
 *
 * @package
 * @author Alexis Eric
 * @copyright Copyright (c) 2012 - Erra Prod
 * @version $1.0$
 * @access public
 */
class Tools_StatsObject extends Erra_Fields_Base_Object {

    /**
     * getFirstDayThisMonth() - Retourne le premier jour de ce mois
     * 
     * @return type
     */
    public static function getFirstDayThisMonth() {
        return mktime(0, 0, 0, (date('m')), 0, date('Y'));
    }

    /**
     * getLastDayThisMonth() - Retourne le dernier jour de ce mois
     * 
     * @return type
     */
    public static function getLastDayThisMonth() {
        return mktime(0, 0, 0, (date('m') + 1), 0, date('Y'));
    }

    /**
     * getCAThisMonth - Retourne le chiffre d'affaires de ce mois
     * 
     * @return type
     */
    public static function getCAThisMonth() {
        $query = 'SELECT prestation_id, COUNT(*) AS num_prestas, prix FROM msa_slotobject, msa_prestationobject WHERE event_type="presta" AND date_commande >' . self::getFirstDayThisMonth() . ' AND date_commande < ' . self::getLastDayThisMonth() . ' AND msa_slotobject.prestation_id=msa_prestationobject.id GROUP BY prestation_id';
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            while ($row = $queryObject->fetch()) {
                $result += $row->num_prestas * $row->prix;
            }
        }
        return $result;
    }

    /**
     * getRegistrationsThisMonth() - Retourne le nombre d'inscriptions ce mois
     * 
     * @return type
     */
    public static function getRegistrationsThisMonth() {
        $query = 'SELECT COUNT(*) AS num_registrations FROM msa_clientobject WHERE date_creation >' . self::getFirstDayThisMonth() . ' AND date_creation < ' . self::getLastDayThisMonth();
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            $row = $queryObject->fetch();
            $result = $row->num_registrations;
        }
        return $result;
    }

    /**
     * getCommandesThisMonth() - Retourne le nombre de commandes ce mois
     * 
     * @return type
     */
    public static function getCommandesThisMonth() {
        $query = 'SELECT COUNT(*) AS num_commandes FROM msa_slotobject WHERE date_commande >' . self::getFirstDayThisMonth() . ' AND date_commande < ' . self::getLastDayThisMonth();

        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            $row = $queryObject->fetch();
            $result = $row->num_commandes;
        }
        return $result;
    }

    /**
     * getFirstDayThisWeek() - Retourne le premier jour de cette semaine
     * @return type
     */
    public static function getFirstDayThisWeek() {
        return strtotime(date('o-\\WW', time()));
    }

    /**
     * getLastDayThisWeek() - Retourne le dernier jour de la semaine
     * FIXME : cette fonction est incorrecte
     * 
     * @return type
     */
    public static function getLastDayThisWeek() {
        return time();
    }

    /**
     * getCAThisWeek() - Retourne le chiffre d'affairres de cette semaine
     * 
     * @return type
     */
    public static function getCAThisWeek() {
        $query = 'SELECT prestation_id, COUNT(*) AS num_prestas, prix FROM msa_slotobject, msa_prestationobject WHERE event_type="presta" AND date_commande >' . self::getFirstDayThisWeek() . ' AND date_commande < ' . self::getLastDayThisWeek() . ' AND msa_slotobject.prestation_id=msa_prestationobject.id GROUP BY prestation_id';
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            while ($row = $queryObject->fetch()) {
                $result += $row->num_prestas * $row->prix;
            }
        }
        return $result;
    }

    /**
     * getRegistrationsThisWeek() - Retourne le nombre d'inscriptions de cette semaine
     * 
     * @return type
     */
    public static function getRegistrationsThisWeek() {
        $query = 'SELECT COUNT(*) AS num_registrations FROM msa_clientobject WHERE date_creation >' . self::getFirstDayThisWeek() . ' AND date_creation < ' . self::getLastDayThisWeek();
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            $row = $queryObject->fetch();
            $result = $row->num_registrations;
        }
        return $result;
    }

    /**
     * 
     */
    public static function getCommandesThisWeek() {
        $query = 'SELECT COUNT(*) AS num_commandes FROM msa_slotobject WHERE date_commande >' . self::getFirstDayThisWeek() . ' AND date_commande < ' . self::getLastDayThisWeek();

        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $result = 0;
        if (Erra_Core_DatabasePdo::rowCount()) {
            $row = $queryObject->fetch();
            $result = $row->num_commandes;
        }
        return $result;
    }

    /**
     * getCommandesByTime() - Retourne le nombre de commandes en fonction du temps
     * 
     * @param type $shop
     * @return type
     */
    public static function getCommandesByTime($shop = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $query = 'SELECT date_commande FROM msa_slotobject WHERE event_type="presta" ' . $extraLocation . ' ORDER BY date_commande ASC';
        $result = self::fetchStockResult($query);
        $return['title'] = 'Tous les rendez-vous';
        $return['serie_name'] = 'Rendez-vous';
        $return['chart_data'] = $result;
        return $return;
    }

    /**
     * getCommandesCaByTime() - Retourne le chiffre d'affaire des commandes dans le temps
     * 
     * @param type $shop
     * @return type
     */
    public static function getCommandesCaByTime($shop = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $query = 'SELECT date_commande, prix FROM msa_slotobject, msa_prestationobject WHERE event_type="presta" ' . $extraLocation . '  AND msa_slotobject.prestation_id=msa_prestationobject.id ORDER BY date_commande ASC';
        $result = self::fetchStockResult($query);
        $return['title'] = "Chiffre d'affaires réalisé";
        $return['serie_name'] = "Chiffre d'affaires";
        $return['chart_data'] = $result;
        return $return;
    }

    /**
     * getCommandesInternet() - Retourne les commandes internet
     * 
     * @param type $shop
     * @return type
     */
    public static function getCommandesInternet($shop = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $query = 'SELECT date_commande FROM msa_slotobject WHERE event_type="presta" AND from_internet=1 ' . $extraLocation . ' ORDER BY date_commande ASC';
        $result = self::fetchStockResult($query);
        $return['title'] = 'Les commandes prises par Internet';
        $return['serie_name'] = 'Rendez-vous';
        $return['chart_data'] = $result;
        return $return;
    }

    /**
     * getCAPasVenu() - Retourne le chiffre d'affaire de ceux qui ne sont psa venus
     * 
     * @param type $shop
     * @return type
     */
    public static function getCAPasVenu($shop = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $query = 'SELECT date_commande, prix FROM msa_slotobject, msa_prestationobject WHERE event_type="presta" AND status_rdv="pas_venu" ' . $extraLocation . ' AND msa_slotobject.prestation_id = msa_prestationobject.id ORDER BY date_commande ASC';
        $result = self::fetchStockResult($query, 'prix');
        $return['title'] = "Chiffre d'affaires des clients non venus";
        $return['serie_name'] = '€';
        $return['chart_data'] = $result;
        return $return;
    }

    /**
     * getLastCommandes() - Retourne les $count dernières commandes
     * 
     * @param type $count
     * @return type
     */
    public static function getLastCommandes($count) {
        $query = 'SELECT msa_clientobject.nom AS nom_client, msa_clientobject.prenom AS prenom_client, msa_clientobject.object_id AS client_object_id, prestation_name, date_commande, location FROM msa_clientobject, msa_slotobject, msa_agendaobject, msa_prestationobject WHERE msa_slotobject.prestation_id = msa_prestationobject.id AND msa_slotobject.agenda_id = msa_agendaobject.id AND msa_slotobject.client_id = msa_clientobject.id ORDER BY date_commande DESC LIMIT 0 , ' . $count;
        $result = array();
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $savedShops = array();
        if (Erra_Core_DatabasePdo::rowCount()) {
            while ($row = $queryObject->fetch()) {
                // Lors de l'import de rendez-vous, l'information sur la boutique n'est pas disponible. Pour éviter que la requete ci
                // dessus soit vide, on récupère l'information sur les boutiques ici
                if (is_numeric($row->location)) {
                    if (isset($savedShops[$row->location])) {
                        $row->nom_magasin = $savedShops[$row->location];
                    } else {
                        $shopQuery = Erra_Core_DatabasePdo::SELECT('SELECT nom_magasin FROM msa_locationobject WHERE id="' . $row->location . '"');
                        if (Erra_Core_DatabasePdo::rowCount() && is_object($shopQuery)) {
                            $shopQuery = $shopQuery->fetch();
                            $row->nom_magasin = $shopQuery->nom_magasin;
                            $savedShops[$row->location] = $shopQuery->nom_magasin; // Save in cache 
                        }
                    }
                } else {
                    $row->nom_magasin = '-';
                }
                $result[] = $row;
            }
        }
        return $result;
    }

    /**
     * dataGetBestClients() - Retourne la liste des meilleurs clients
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @param type $count
     * @return string
     */
    public static function dataGetBestClients($shop = null, $start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT client_id, nom, prenom, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_clientobject WHERE event_type="presta" AND msa_slotobject.client_id = msa_clientobject.id ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' GROUP BY client_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        } else {
            $query = 'SELECT client_id, nom, prenom, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_clientobject WHERE event_type="presta" AND msa_slotobject.client_id = msa_clientobject.id ' . $extraLocation . ' GROUP BY client_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->nom . ' ' . $row->prenom . '"';
                $chart_data[] = $row->num_prestas;
            }
        }
        $result = array();
        $result['title'] = 'Les meilleurs clients';
        $result['y_axis_title'] = 'Nombre de rendez-vous';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        $result['chart_serie_name'] = 'Nombre de rendez-vous';
        return $result;
    }

    /**
     * dataGetBestPrestations() - Retourne les meilleures prestations
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @param type $count
     * @return string
     */
    public static function dataGetBestPrestations($shop = null, $start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT prestation_name, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_prestationobject, msa_typeprestationobject WHERE event_type="presta" AND msa_slotobject.prestation_id = msa_prestationobject.id ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end . 'AND msa_typeprestationobject.id=msa_prestationobject.prestation_type_id  GROUP BY prestation_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        } else {
            $query = 'SELECT prestation_name, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_prestationobject, msa_typeprestationobject WHERE event_type="presta" AND msa_slotobject.prestation_id = msa_prestationobject.id ' . $extraLocation . ' AND msa_typeprestationobject.id=msa_prestationobject.prestation_type_id  GROUP BY prestation_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->prestation_name . '"';
                $chart_data[] = $row->num_prestas;
            }
        }
        $result = array();
        $result['title'] = 'Les meilleures prestations';
        $result['y_axis_title'] = 'Nombre de rendez-vous';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        $result['chart_serie_name'] = 'Nombre de rendez-vous';
        return $result;
    }

    /**
     * dataGetBestAgendasUse() - Retourne les agendas les plus utilisés
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @param type $count
     * @return string
     */
    public static function dataGetBestAgendasUse($shop = null, $start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject WHERE event_type="presta" AND msa_slotobject.agenda_id = msa_agendaobject.id ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' AND msa_agendaobject.location=msa_locationobject.id  GROUP BY agenda_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        } else {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject WHERE event_type="presta" AND msa_slotobject.agenda_id = msa_agendaobject.id ' . $extraLocation . ' AND msa_agendaobject.location=msa_locationobject.id  GROUP BY agenda_id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->nom_agenda . ' (' . $row->nom_location . ')"';
                $chart_data[] = $row->num_prestas;
            }
        }
        $result = array();
        $result['title'] = 'Les agendas les plus bookés';
        $result['y_axis_title'] = 'Nombre de rendez-vous';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        $result['chart_serie_name'] = 'Nombre de rendez-vous';
        return $result;
    }

    /**
     * dataGetBestAgendasUseTime() - Retourne les agendas les plus utilisés dans le temps
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @param type $count
     * @return string
     */
    public static function dataGetBestAgendasUseTime($shop = null, $start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, SUM( msa_prestationobject.duree ) AS duree_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject, msa_prestationobject WHERE event_type = "presta" AND msa_slotobject.agenda_id = msa_agendaobject.id ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' AND msa_slotobject.prestation_id= msa_prestationobject.id AND msa_agendaobject.location = msa_locationobject.id GROUP BY agenda_id ORDER BY duree_prestas DESC LIMIT 0 , ' . $count;
        } else {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, SUM( msa_prestationobject.duree ) AS duree_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject, msa_prestationobject WHERE event_type = "presta" AND msa_slotobject.agenda_id = msa_agendaobject.id ' . $extraLocation . ' AND msa_slotobject.prestation_id= msa_prestationobject.id AND msa_agendaobject.location = msa_locationobject.id GROUP BY agenda_id ORDER BY duree_prestas DESC LIMIT 0 , ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->nom_agenda . ' (' . $row->nom_location . ')"';
                $duree = $row->duree_prestas;
                $duree = number_format($duree / 60, 1);
                $chart_data[] = $duree;
            }
        }
        $result = array();
        $result['title'] = 'Les agendas les plus utilisés';
        $result['y_axis_title'] = 'Heures travaillées';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        $result['chart_serie_name'] = 'Heures travaillées';
        return $result;
    }

    /**
     * dataGetLocationUse() - Retourne a liste des lieux les plus fréquentés
     * 
     * @param type $start
     * @param type $end
     * @param type $count
     * @return string
     */
    public static function dataGetLocationUse($start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject WHERE event_type="presta" AND msa_slotobject.agenda_id = msa_agendaobject.id AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' AND msa_agendaobject.location=msa_locationobject.id  GROUP BY msa_locationobject.id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        } else {
            $query = 'SELECT nom_agenda, msa_locationobject.nom AS nom_location, COUNT(msa_slotobject.id) AS num_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject WHERE event_type="presta" AND msa_slotobject.agenda_id = msa_agendaobject.id AND msa_agendaobject.location=msa_locationobject.id  GROUP BY msa_locationobject.id ORDER BY num_prestas DESC LIMIT 0, ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->nom_location . '"';
                $chart_data[] = $row->num_prestas;
            }
        }
        $result = array();
        $result['title'] = 'Les lieux les plus bookés';
        $result['y_axis_title'] = 'Nombre de rendez-vous';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        $result['chart_serie_name'] = 'Nombre de rendez-vous';
        return $result;
    }

    /**
     * dataGetLocationUseTime() - Retourne les lieux les plus frequentés dans le temps
     * 
     * @param type $start
     * @param type $end
     * @param type $count
     * @return type
     */
    public static function dataGetLocationUseTime($start = null, $end = null, $count = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT msa_locationobject.nom AS nom_location, SUM( msa_prestationobject.duree ) AS duree_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject, msa_prestationobject WHERE event_type = "presta" AND msa_slotobject.agenda_id = msa_agendaobject.id  AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' AND msa_slotobject.prestation_id= msa_prestationobject.id AND msa_agendaobject.location = msa_locationobject.id GROUP BY msa_locationobject.id ORDER BY duree_prestas DESC LIMIT 0 , ' . $count;
        } else {
            $query = 'SELECT msa_locationobject.nom AS nom_location, SUM( msa_prestationobject.duree ) AS duree_prestas FROM msa_slotobject, msa_agendaobject, msa_locationobject, msa_prestationobject WHERE event_type = "presta" AND msa_slotobject.agenda_id = msa_agendaobject.id AND msa_slotobject.prestation_id= msa_prestationobject.id AND msa_agendaobject.location = msa_locationobject.id GROUP BY msa_locationobject.id ORDER BY duree_prestas DESC LIMIT 0 , ' . $count;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $chart_x_axis = array();
        $chart_data = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $chart_x_axis[] = '"' . $row->nom_location . '"';
                $duree = $row->duree_prestas;
                $duree = number_format($duree / 60, 1);
                $chart_data[] = $duree;
            }
        }
        $result = array();
        $result['title'] = 'Les lieux par taux de remplissage';
        $result['chart_serie_name'] = 'Heures travaillées';
        $result['y_axis_title'] = 'Heures travaillées';
        $result['chart_x_axis'] = $chart_x_axis;
        $result['chart_data'] = $chart_data;
        return $result;
    }

    /**
     * dataGetCommandeRatio() - Retourne le ratio des commandes internet/commandes normales
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @return type
     */
    public static function dataGetCommandeRatio($shop = null, $start = null, $end = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = 'SELECT COUNT(id) AS internet FROM msa_slotobject WHERE from_internet=1 AND event_type="presta" UNION SELECT COUNT(id) FROM msa_slotobject WHERE from_internet=0 AND event_type="presta" ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end;
        } else {
            $query = 'SELECT COUNT(id) AS internet FROM msa_slotobject WHERE from_internet=1 AND event_type="presta" UNION SELECT COUNT(id) FROM msa_slotobject WHERE from_internet=0 AND event_type="presta" ' . $extraLocation;
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        $count = array();
        if (is_object($queryObject)) {
            while ($row = $queryObject->fetch()) {
                $count[] = $row->internet;
            }
        }
        $total = $count[0] + $count[1];
        if ($total == 0) { // Prevent integer division by zero
            $count[0] = $count[1] = 1;
            $total = 2;
        }
        $internetRatio = array('Internet', (float) number_format(($count[0] / $total) * 100, 1));
        $standardRatio = array('Standard', (float) number_format(($count[1] / $total) * 100, 1));

        $result = array();
        $result['title'] = 'Ratio de commandes standard/internet';
        $result['chart_serie_name'] = 'Pourcentage de commandes';
        $result['chart_data'] = array($internetRatio, $standardRatio);

        return $result;
    }

    /**
     * dataGetCategoriesRatio() - Retourne les rations par catégorie
     * 
     * @param type $shop
     * @param type $start
     * @param type $end
     * @return type
     */
    public static function dataGetCategoriesRatio($shop = null, $start = null, $end = null) {
        $extraLocation = (is_numeric($shop)) ? 'AND agenda_id IN (SELECT id FROM msa_agendaobject WHERE location=' . $shop . ')' : '';
        $count = (is_null($count) || !is_numeric($count)) ? 10 : 0;
        if (is_numeric($start) && is_numeric($end)) {
            $query = ' SELECT COUNT(msa_slotobject.id) as num_prestas, prestation_type_id FROM msa_slotobject, msa_prestationobject, msa_typeprestationobject WHERE event_type = "presta" ' . $extraLocation . ' AND date_commande > ' . $start . ' AND date_commande < ' . $end . ' AND msa_slotobject.prestation_id = msa_prestationobject.id AND msa_typeprestationobject.id = msa_prestationobject.prestation_type_id GROUP BY prestation_type_id';
        } else {
            $query = ' SELECT COUNT(msa_slotobject.id) as num_prestas, prestation_type_id FROM msa_slotobject, msa_prestationobject, msa_typeprestationobject WHERE event_type = "presta" ' . $extraLocation . ' AND msa_slotobject.prestation_id = msa_prestationobject.id AND msa_typeprestationobject.id = msa_prestationobject.prestation_type_id GROUP BY prestation_type_id';
        }
        $queryObject = Erra_Core_DatabasePdo::SELECT($query);
        if (is_object($queryObject)) {
            $sum = 0;
            $count = array();
            $typesArray = array();
            while ($row = $queryObject->fetch()) {
                $typesArray[] = $row->prestation_type_id;
                $count[$row->prestation_type_id] = $row->num_prestas;
                $sum += $row->num_prestas;
            }
            // Now build our array
            $queryObject = Erra_Core_DatabasePdo::SELECT('SELECT id, prestation_type_name FROM msa_typeprestationobject WHERE id IN(' . implode(',', $typesArray) . ')');
            if (Erra_Core_DatabasePdo::rowCount() && count($count) && $sum > 0) {
                $chart_data = array();
                while ($row = $queryObject->fetch()) {
                    $val = $count[$row->id];
                    $title = $row->prestation_type_name;
                    $chart_data[] = array($title, (float) number_format(($val / $sum) * 100, 1));
                }
            }
        }
        $result = array();
        $result['title'] = 'Types de commande';
        $result['chart_serie_name'] = 'Types de commande';
        $result['chart_data'] = $chart_data;

        return $result;
    }

    /**
     * dataGetBestPrestationsEffectueByAgenda() - Retourne le resultat contient des prestations les plus effectué
     * @param type $id_agenda
     * @return type
     */
    public static function dataGetBestPrestationsEffectueByAgenda($id_agenda) {
        $query = "SELECT count(prestation.id) as num_prestas, prestation.prestation_name as name FROM msa_prestationobject as prestation
                INNER JOIN msa_slotobject as slot ON prestation.id = slot.prestation_id
                Inner Join msa_agendaobject as agenda ON agenda.id = slot.agenda_id ";
        $query .= ' Where slot.status_rdv = "Venu" AND prestation.id != 0 ';
        if (is_numeric($id_agenda)) {
            $query .= " And agenda.id = $id_agenda ";
        }
        $query .= ' GROUP BY prestation.id
        ORDER BY num_prestas DESC
        LIMIT 8';

        $queryResult = Erra_Core_DatabasePdo::SELECT($query);
        $chart_data = array();
        $chart_x_axis = array();
        if ($queryResult !== false) {
            while ($row = $queryResult->fetch()) {
                $chart_x_axis[] = '"' . $row->name . '"';
                $chart_data[] = $row->num_prestas;
            }
        }

        $result = array();

        $result['title'] = 'Les prestations les plus effectués';

        $result['y_axis_title'] = 'Prestations effectués';

        $result['chart_serie_name'] = 'Effectué';

        $result['chart_x_axis'] = $chart_x_axis;

        $result['chart_data'] = $chart_data;

        return $result;
    }

    public static function dataGetTauxRemplissageAgenda($lieux_id) {
        $sub_query = "";

        if (is_numeric($lieux_id)) {
            $sub_query .= ' where agenda.location = ' . $lieux_id . ' ';
        }
        $query_count = 'SELECT count(slot.id)/(SELECT count(slot.id) 
                        From msa_agendaobject as agenda 
                        left join msa_slotobject as slot 
                        on slot.agenda_id = agenda.id
                        where slot.prestation_id != 0)' . $sub_query . ' 
            as taux, 
            agenda.id as id,
            agenda.nom_agenda as name,
            count(slot.id) as count
            From msa_agendaobject as agenda
            left join msa_slotobject as slot on slot.agenda_id = agenda.id
            where slot.prestation_id != 0
            group by agenda.id' . $sub_query;
        $query_result = Erra_Core_DatabasePdo::SELECT($query_count);

        $chart_data = array();
        $chart_x_axis = array();

        if ($query_result !== false) {
            while ($row = $query_result->fetch()) {
                $chart_x_axis[] = '"' . $row->name . '"';
                $cur_char_data ="{y:".(number_format($row->taux, 2) * 100).", line:[ ";
                $new_categorie = "categories:[ ";
                
                $cur_agenda_id = $row->id;
                $query_taux_temp = "SELECT slot_day as date, count(*) as count FROM msa_slotobject as slot 
                                    Where agenda_id = $cur_agenda_id and slot.prestation_id != 0
                                    Group by date
                                    order by date";
                $query_taux_temp_result  = Erra_Core_DatabasePdo::SELECT($query_taux_temp);
                
                if($query_taux_temp_result !== FALSE){
                    $cur_amount = 0;
                    while($row = $query_taux_temp_result->fetch()){
                        $cur_amount += $row->count; 
                        $cur_char_data .= '["'.date('d/m/Y', $row->date).'",'.$cur_amount."],";
                        $new_categorie .= '\''.date('d/m/Y', $row->date)."',";
                    }
                }
                
                $new_categorie = substr($new_categorie, 0, strlen($new_categorie)-1);
                
                $cur_char_data = substr($cur_char_data, 0, strlen($cur_char_data)-1).'],'.$new_categorie.' ]}';
                
               // echo $cur_char_data.'<br/>';
                $chart_data[] = $cur_char_data;
            }
        }
        $result = array();

        $result['title'] = 'Le taux de remplissage';

       // $result['y_axis_title'] = 'Pourcentage de remplissage';

        $result['chart_x_axis'] = $chart_x_axis;

        $result['chart_serie_name'] = 'Pourcentage';

        $result['chart_data'] = $chart_data;

        return $result;
    }

    /**
     * fetchStockResult() - Calcul le resultat d'une requete
     * 
     * @param type $query
     * @param type $secondDimension
     * @return type
     */
    public static function fetchStockResult($query, $secondDimension = null) {
        $result = array();
        $tmpData = array();
        $statsDataQuery = Erra_Core_DatabasePdo::SELECT($query);

        if (Erra_Core_DatabasePdo::rowCount()) {
            while ($statsData = $statsDataQuery->fetch()) {
                $date_commande = $statsData->date_commande;
                $dayIndex = (string) (strtotime(date('m/d/Y', $date_commande)) * 1000);
                if ((integer) $dayIndex > 0) {
                    if (is_null($secondDimension)) {
                        if (!isset($tmpData[$dayIndex])) {
                            if (isset($statsData->prix)) {
                                $tmpData[$dayIndex] = $statsData->prix;
                            } else {
                                $tmpData[$dayIndex] = 1;
                            }
                        } else {
                            if (isset($statsData->prix)) {
                                $tmpData[$dayIndex] += $statsData->prix;
                            } else {
                                $tmpData[$dayIndex] += 1;
                            }
                        }
                    } else {
                        if (!isset($tmpData[$dayIndex])) {
                            $tmpData[$dayIndex] = $statsData->$secondDimension;
                        } else {
                            $tmpData[$dayIndex]+=$statsData->$secondDimension;
                        }
                    }
                }
            }
        }
        // Parse back
        if (is_array($tmpData) && count($tmpData)) {
            foreach ($tmpData as $day => $occurencies) {
                $result[] = array($day, $occurencies);
            }
        }
        return $result;
    }

}

?>
